{*******************************************************************************
* The contents of this file are used with permission, subject to the Mozilla   *
* Public License Version 1.1 (the "License"); you may not use this file except *
* in compliance with the License. You may obtain a copy of the License at      *
* http://www.mozilla.org/MPL/                                                  *
*                                                                              *
* Software distributed under the License is distributed on an "AS IS" basis,   *
* WITHOUT WARRANTY OF ANY KIND, either express or implied. See the License for *
* the specific language governing rights and limitations under the License.    *
*                                                                              *
* The Original Code is distributed as part of the "mediate" product and is     *
* Copyright (C) @home media limited. All Rights Reserved.                      *
*                                                                              *
* Email: support@athomemedia.co.uk                                             *
* Web:   http://www.athomemedia.co.uk                                          *
*******************************************************************************}

unit MediaSchema;

(*
  Media schema explanatory notes
  ==============================

  This schema forms a media repository and has details of all known media
  files. It comprises the following tables:

  MediaItem - contains individual media items (e.g. Mp3 track)
  Album - contains contains attributes for media albums (i.e. Folders)
  Artist - contains unique author/artist names for all media items & albums
  Genre - contains unique genre names for all media items & albums

  MediaItem
  ---------

  This table has one row per media item (a single track or item of content)
  and columns for media name (title), artist, genre, file url, year and size.

  - Id is a system generated integer acting as primary key
  - Name is a mixed case title intended for display within the mediate UI.
  - AlbumId is a foreign key to id in Album table.
  - ArtistId is a foreign key to id in Artist table.
  - GenreId is a foreign key to id in Genre table.
  - File URL contains absolute path to media file (UTF-8 encoded).
  - Year is an integer value for published year.
  - Size is duration in seconds for media item.
  - Track is track number / position within parent album
  - BitRate is the encoding bitrate in bits per second for this item
  - AlbumArt is a boolean flag (0/1) indicating whether item has embedded art

  Album
  -----

  This table has one row per media album (a collection of media items) and
  columns for album name (title), artist, genre and year.

  - Id is a system generated integer acting as primary key
  - Name is a mixed case title intended for display within the mediate UI.
  - ArtistId is a foreign key to id in Artist table.
  - GenreId is a foreign key to id in Genre table.
  - Year is an integer value for published year.
  - Image URL contains absolute path to album art file (UTF-8 encoded).
  - Size is the total duration in seconds for this album
  - Tracks is the total number of tracks for this album

  Artist
  ------

  This table has one row per unique author (artist) and column for artist name.

  - Id is a system generated integer acting as primary key
  - Name is a mixed case title intended for display within the mediate UI.
  - Size is the total duration in seconds for this artist
  - Tracks is the total number of tracks stored for this artist
  - Image URL contains absolute path to album art file (UTF-8 encoded).

  Genre
  -----

  This table has one row per unique genre (category) and column for genre name.

  - Id is a system generated integer acting as primary key
  - Name is a mixed case title intended for display within the mediate UI.
  - Size is the total duration in seconds for this genre
  - Tracks is the total number of tracks stored for this genre
*)

interface

uses Classes, DBSchema;

const
  MEDIA_DB_FILENAME = 'media.db3';

  // Define common field positions used in queries below. We should adhere
  // to this standard wherever possible so we don't need lots of field defs.
  FIELD_ID = 0;
  FIELD_NAME = 1;
  FIELD_SIZE = 2;
  FIELD_TRACK = 3;
  FIELD_URL = 4;
  FIELD_YEAR = 5;
  FIELD_GENRE = 6;
  FIELD_ARTIST = 7;
  FIELD_ALBUM = 8;
  FIELD_BITRATE = 9;
  FIELD_IMAGE = 10;

type
  // Lightweight cache for genre details
  TGenreInfo = record
    Name: String;
    Duration: Integer;
    Tracks: Integer;
  end;

  // Lightweight cache for artist details
  TArtistInfo = record
    Name: String;
    Duration: Integer;
    Tracks: Integer;
    ImageURL: String;
  end;

  // Lightweight cache for album details
  TAlbumInfo = record
    Name: String;
    Duration: Integer;
    Tracks: Integer;
    ImageURL: String;
    Year: Integer;
    GenreId: Integer;
    ArtistId: Integer;
  end;

  PGenreInfo = ^TGenreInfo;
  PArtistInfo = ^TArtistInfo;
  PAlbumInfo = ^TAlbumInfo;

  TAHMMediaSchema = class(TAHMDBSchema)
  private
    FPrepared: Boolean;
    FCached: Boolean;
    FInsertMedia: TAHMDBQuery;
    FInsertGenre: TAHMDBQuery;
    FInsertArtist: TAHMDBQuery;
    FInsertAlbum: TAHMDBQuery;
    FArtistCache: TList;
    FAlbumCache: TList;
    FGenreCache: TList;
  protected
    function GetGenreInfo(Index: Integer): PGenreInfo;
    function GetGenreCount: Integer;
    function GetArtistInfo(Index: Integer): PArtistInfo;
    function GetArtistCount: Integer;
    function GetAlbumInfo(Index: Integer): PAlbumInfo;
    function GetAlbumCount: Integer;
  public
    constructor Create;
    destructor Destroy; override;
    property Genres[Index: Integer]: PGenreInfo read GetGenreInfo;
    property GenreCount: Integer read GetGenreCount;
    property Artists[Index: Integer]: PArtistInfo read GetArtistInfo;
    property ArtistCount: Integer read GetArtistCount;
    property Albums[Index: Integer]: PAlbumInfo read GetAlbumInfo;
    property AlbumCount: Integer read GetAlbumCount;
    function CheckSchema: Boolean; override;
    procedure BuildIndexes;
    procedure PopulateTestData;
    procedure BuildCaches;
    procedure ClearCaches;
    procedure PrepareInsertQueries;
    procedure ReleaseInsertQueries;
    procedure InsertMediaItem(Id: Integer; const Name: String;
                              AlbumId, ArtistId, GenreId: Integer;
                              const URL: String; Year, Size, Track, BitRate: Integer;
                              AlbumArt: Boolean);
    procedure InsertGenre(Id: Integer; const Name: String; Size, Tracks: Integer);
    procedure InsertArtist(Id: Integer; const Name, ImageURL: String;
                           Size, Tracks: Integer);
    procedure InsertAlbum(Id: Integer; const Name, ImageURL: String;
                          ArtistId, GenreId, Year, Size, Tracks: Integer);
    procedure UpdateArtistArt(Id: Integer; const ImageURL: String);
    procedure UpdateAlbumArt(Id: Integer; const ImageURL: String);
    function ListGenres: TAHMDBResult;
    function ListArtists(GenreId: Integer; CompilationArtists: Boolean): TAHMDBResult;
    function ListAlbums(GenreId, ArtistId: Integer): TAHMDBResult;
    function ListTracks(GenreId, ArtistId, AlbumId: Integer): TAHMDBResult;
    function GetAlbumHash(AlbumId: Integer): String;
  end;

implementation

uses SysUtils;

const
  // Table DML
  // =========

  // Media Items
  TBL_MEDIAITEM = 'MediaItem';
  SQL_BUILD_MEDIA_TABLE = 'CREATE TABLE MediaItem (' +
                          '[Id] INTEGER PRIMARY KEY, ' +
                          '[Name] CHAR, ' +
                          '[AlbumId] INTEGER, ' +
                          '[ArtistId] INTEGER, ' +
                          '[GenreId] INTEGER, ' +
                          '[FileURL] CHAR, ' +
                          '[Year] INTEGER, ' +
                          '[Size] INTEGER, ' +
                          '[Track] INTEGER, ' +
                          '[BitRate] INTEGER, ' +
                          '[AlbumArt] INTEGER)';
  SQL_BUILD_MEDIA_INDEX1 = 'CREATE INDEX MIAlbumId ON [MediaItem]([AlbumId])';
  SQL_BUILD_MEDIA_INDEX2 = 'CREATE INDEX MIArtistId ON [MediaItem]([ArtistId])';
  SQL_BUILD_MEDIA_INDEX3 = 'CREATE INDEX MIName ON [MediaItem]([Name])';
  SQL_UPGRADE_MEDIA_TABLE_0 = 'ALTER TABLE MediaItem ADD COLUMN [Track] INTEGER';
  SQL_UPGRADE_MEDIA_TABLE_1 = 'ALTER TABLE MediaItem ADD COLUMN [BitRate] INTEGER';
  SQL_UPGRADE_MEDIA_TABLE_2 = 'ALTER TABLE MediaItem ADD COLUMN [AlbumArt] INTEGER';

  // Albums
  TBL_ALBUM = 'Album';
  SQL_BUILD_ALBUM_TABLE = 'CREATE TABLE Album (' +
                          '[Id] INTEGER PRIMARY KEY, ' +
                          '[Name] CHAR, ' +
                          '[ArtistId] INTEGER, ' +
                          '[GenreId] INTEGER, ' +
                          '[Year] INTEGER, ' +
                          '[ImageURL] CHAR, ' +
                          '[Size] INTEGER, ' +
                          '[Tracks] INTEGER)';
  SQL_BUILD_ALBUM_INDEX1 = 'CREATE INDEX ALArtistId ON [Album]([ArtistId])';
  SQL_BUILD_ALBUM_INDEX2 = 'CREATE INDEX ALGenreId ON [Album]([GenreId])';
  SQL_UPGRADE_ALBUM_TABLE_0 = 'ALTER TABLE Album ADD COLUMN [ImageURL] CHAR';
  SQL_UPGRADE_ALBUM_TABLE_1 = 'ALTER TABLE Album ADD COLUMN [Size] INTEGER';
  SQL_UPGRADE_ALBUM_TABLE_2 = 'ALTER TABLE Album ADD COLUMN [Tracks] INTEGER';

  // Artists
  TBL_ARTIST = 'Artist';
  SQL_BUILD_ARTIST_TABLE = 'CREATE TABLE Artist (' +
                           '[Id] INTEGER PRIMARY KEY, ' +
                           '[Name] CHAR, ' +
                           '[Size] INTEGER, ' +
                           '[Tracks] INTEGER, ' +
                           '[ImageURL] CHAR)';
  SQL_UPGRADE_ARTIST_TABLE_0 = 'ALTER TABLE Artist ADD COLUMN [Size] INTEGER';
  SQL_UPGRADE_ARTIST_TABLE_1 = 'ALTER TABLE Artist ADD COLUMN [Tracks] INTEGER';
  SQL_UPGRADE_ARTIST_TABLE_2 = 'ALTER TABLE Artist ADD COLUMN [ImageURL] CHAR';

  // Genres
  TBL_GENRE = 'Genre';
  SQL_BUILD_GENRE_TABLE = 'CREATE TABLE Genre (' +
                          '[Id] INTEGER PRIMARY KEY, ' +
                          '[Name] CHAR, ' +
                          '[Size] INTEGER, ' +
                          '[Tracks] INTEGER)';
  SQL_UPGRADE_GENRE_TABLE_0 = 'ALTER TABLE Genre ADD COLUMN [Size] INTEGER';
  SQL_UPGRADE_GENRE_TABLE_1 = 'ALTER TABLE Genre ADD COLUMN [Tracks] INTEGER';

  // Insert statements
  SQL_INSERT_MEDIA = 'INSERT INTO MediaItem(Id, Name, AlbumId, ArtistId, ' +
                     'GenreId, FileURL, Year, Size, Track, BitRate, AlbumArt) ' +
                     'VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11)';
  SQL_INSERT_ALBUM = 'INSERT INTO Album(Id, Name, ArtistId, GenreId, Year, ' +
                     'ImageURL, Size, Tracks) VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8)';
  SQL_INSERT_GENRE = 'INSERT INTO Genre(Id, Name, Size, Tracks) VALUES (?1, ?2, ?3, ?4)';
  SQL_INSERT_ARTIST = 'INSERT INTO Artist(Id, Name, Size, Tracks, ImageURL) VALUES (?1, ?2, ?3, ?4, ?5)';

  // Update statements
  SQL_UPDATE_ARTISTART = 'UPDATE Artist SET ImageURL="%1:s" WHERE Id=%0:d';
  SQL_UPDATE_ALBUMART = 'UPDATE Album SET ImageURL="%1:s" WHERE Id=%0:d';


  // Queries
  // =======

  // Retrieve all Genres (Must be at least one artist for each genre)
  SQL_GET_ALL_GENRES = 'SELECT Id, Name, Size, Tracks FROM Genre WHERE Id <> 0 ORDER BY Name';

  // Retrieve all Genres for cache population (naturally will be in Id order)
  SQL_GET_GENRE_CACHE = 'SELECT Id, Name, Size, Tracks FROM Genre';

  // Retrieve all Artists (Must be at least one album for each artist)
  SQL_GET_ALL_ARTISTS = 'SELECT Id, Name, Size, Tracks, ImageURL FROM Artist WHERE Id <> 0 ';

  // Retrieve all Artists for cache population (naturally will be in Id order)
  SQL_GET_ARTIST_CACHE = 'SELECT Id, Name, Size, Tracks, ImageURL FROM Artist';

  // Retrieve Artists by Genre
  SQL_GET_ARTISTS_BY_GENRE = 'SELECT Id, Name, Size, Tracks, ImageURL FROM Artist WHERE Id IN ' +
                             '(SELECT ArtistId FROM MediaItem WHERE GenreId=%0:d) ';

  // Additional filter term to retrieve album artists only
  SQL_ARTIST_ALBUM = 'AND Id IN (SELECT ArtistId FROM Album) ';

  // Additional term to order artists
  SQL_ARTIST_ORDER = 'ORDER BY Name';

  // Retrieve all Albums
  SQL_GET_ALL_ALBUMS = 'SELECT Id, Name, Size, Tracks, ImageURL, Year, GenreId, ArtistId FROM Album WHERE Id <> 0 ';

  // Retrieve all Genres for cache population (must be in Id order)
  SQL_GET_ALBUM_CACHE = 'SELECT Id, Name, Size, Tracks, ImageURL, Year, GenreId, ArtistId FROM Album';

  // Retrieve Albums by Artist
  SQL_GET_ALBUMS_BY_ARTIST = 'SELECT DISTINCT a.Id, a.Name, a.Size, a.Tracks, ' +
                             'a.ImageURL, a.Year, a.GenreId, a.ArtistId ' +
                             'FROM Album a, MediaItem m WHERE a.Id=m.AlbumId ' +
                             'AND m.ArtistId=%0:d ORDER BY a.Name';

  // Retrieve Albums by Genre
  SQL_GET_ALBUMS_BY_GENRE = 'SELECT DISTINCT a.Id, a.Name, a.Size, a.Tracks, ' +
                            'a.ImageURL, a.Year, a.GenreId, a.ArtistId ' +
                            'FROM Album a, MediaItem m WHERE a.Id=m.AlbumId ' +
                            'AND m.GenreId=%0:d ORDER BY a.Name';

  // Retrieve Albums by Artist and Genre
  SQL_GET_ALBUMS_BY_ARTIST_GENRE = 'SELECT DISTINCT a.Id, a.Name, a.Size, ' +
                                   'a.Tracks, a.ImageURL, a.Year, a.GenreId, a.ArtistId ' +
                                   'FROM Album a, MediaItem m WHERE a.Id=m.AlbumId ' +
                                   'AND m.ArtistId=%0:d AND m.GenreId=%1:d ORDER BY a.Name';

  // Retrieve all Tracks
  SQL_GET_ALL_TRACKS = 'SELECT Id, Name, Size, Track, FileURL, Year, GenreId, ' +
                       'ArtistId, AlbumId, BitRate, AlbumArt FROM MediaItem';

  // Additional filter terms to retrieve tracks by genre, artist or album
  SQL_TRACK_GENRE = 'GenreId=%0:d';
  SQL_TRACK_ARTIST = 'ArtistId=%0:d';
  SQL_TRACK_ALBUM = 'AlbumId=%0:d';


// TAHMMediaSchema

constructor TAHMMediaSchema.Create;
begin
  inherited;

  // Promote minimum required version
  MinVersion := 0;

  // Create caches for populating media details
  FArtistCache := TList.Create;
  FAlbumCache := TList.Create;
  FGenreCache := TList.Create;
end;

destructor TAHMMediaSchema.Destroy;
begin
  // Release caches
  ClearCaches;
  FArtistCache.Free;
  FAlbumCache.Free;
  FGenreCache.Free;

  inherited;
end;

function TAHMMediaSchema.GetGenreInfo(Index: Integer): PGenreInfo;
begin
  // Retrieve genre info from local cache
  if FCached then Result := FGenreCache[Index]
             else Result := nil;
end;

function TAHMMediaSchema.GetGenreCount: Integer;
begin
  // Retrieve count of cached genres
  if FCached then Result := FGenreCache.Count
             else Result := 0;
end;

function TAHMMediaSchema.GetArtistInfo(Index: Integer): PArtistInfo;
begin
  // Retrieve artist info from local cache
  if FCached then Result := FArtistCache[Index]
             else Result := nil;
end;

function TAHMMediaSchema.GetArtistCount: Integer;
begin
  // Retrieve count of cached artists
  if FCached then Result := FArtistCache.Count
             else Result := 0;
end;

function TAHMMediaSchema.GetAlbumInfo(Index: Integer): PAlbumInfo;
begin
  // Retrieve album name info local cache
  if FCached then Result := FAlbumCache[Index]
             else Result := nil;
end;

function TAHMMediaSchema.GetAlbumCount: Integer;
begin
  // Retrieve count of cached albums
  if FCached then Result := FAlbumCache.Count
             else Result := 0;
end;

function TAHMMediaSchema.CheckSchema: Boolean;
var
  Version: Integer;
begin
  // Set default result - schema is valid
  Result := True;

  // Check installed schema version
  Version := GetSchemaVersion;
  if Version > MinVersion then Exit;

  // Create or upgrade schema as necessary
  if Version < 0 then
  begin
    // Create new schema
    if not FDB.TableExists(TBL_MEDIAITEM) then
      FDB.ExecSQL(SQL_BUILD_MEDIA_TABLE);

    if not FDB.TableExists(TBL_ALBUM) then
      FDB.ExecSQL(SQL_BUILD_ALBUM_TABLE);

    if not FDB.TableExists(TBL_ARTIST) then
      FDB.ExecSQL(SQL_BUILD_ARTIST_TABLE);

    if not FDB.TableExists(TBL_GENRE) then
      FDB.ExecSQL(SQL_BUILD_GENRE_TABLE);
  end
  else if Version = 0 then
  begin
    // Update media items table to include track number, bitrate and art flag
    FDB.ExecSQL(SQL_UPGRADE_MEDIA_TABLE_0);
    FDB.ExecSQL(SQL_UPGRADE_MEDIA_TABLE_1);
    FDB.ExecSQL(SQL_UPGRADE_MEDIA_TABLE_2);

    // Update albums table to include album artwork URL, size and track count
    FDB.ExecSQL(SQL_UPGRADE_ALBUM_TABLE_0);
    FDB.ExecSQL(SQL_UPGRADE_ALBUM_TABLE_1);
    FDB.ExecSQL(SQL_UPGRADE_ALBUM_TABLE_2);

    // Update artists table to include total size, track count and artwork URL
    FDB.ExecSQL(SQL_UPGRADE_ARTIST_TABLE_0);
    FDB.ExecSQL(SQL_UPGRADE_ARTIST_TABLE_1);
    FDB.ExecSQL(SQL_UPGRADE_ARTIST_TABLE_2);

    // Update genre table to include total size and track count
    FDB.ExecSQL(SQL_UPGRADE_GENRE_TABLE_0);
    FDB.ExecSQL(SQL_UPGRADE_GENRE_TABLE_1);
  end
  else
  begin
    // Unknown version number - can't continue
    Result := False;
    Exit;
  end;

  // Update schema version number
  SetSchemaVersion(1);
end;

procedure TAHMMediaSchema.BuildIndexes;
begin
  FDB.ExecSQL(SQL_BUILD_MEDIA_INDEX1);
  FDB.ExecSQL(SQL_BUILD_MEDIA_INDEX2);
//    FDB.ExecSQL(SQL_BUILD_MEDIA_INDEX3);  // name searches not required yet..

  FDB.ExecSQL(SQL_BUILD_ALBUM_INDEX1);
  FDB.ExecSQL(SQL_BUILD_ALBUM_INDEX2);
end;

procedure TAHMMediaSchema.PopulateTestData;
var
  i: Integer;
begin
  FDB.Synchronised := False;
  FDB.BeginTransaction;
  PrepareInsertQueries;
  try
    // Populate some genres
    for i := 1 to 100 do
      InsertGenre(i, 'This is genre #' + IntToStr(i), 600, 50);

    // Populate some artists
    for i := 1 to 1000 do
      InsertArtist(i, 'This is artist #' + IntToStr(i), 'c:\path\folder\blah\cover.jpg',
                   600, 50);

    // Populate some albums
    for i := 1 to 10000 do
      InsertAlbum(i, 'This is album #' + IntToStr(i), 'c:\path\folder\blah\cover.jpg',
                  i mod 1000, i mod 100, 2007, 600, 50);

    // Populate some items
    for i := 1 to 100000 do
      InsertMediaItem(i, 'This is track #' + IntToStr(i), i mod 10000,
                      i mod 1000, i mod 100, 'c:\path\folder\blah\jiz.mp3',
                      2007, 200, i mod 10, 128000, False);
  finally
    ReleaseInsertQueries;
    FDB.Commit;
  end;
end;

procedure TAHMMediaSchema.BuildCaches;
var
  Rows: TAHMDBResult;
  Genre: PGenreInfo;
  Artist: PArtistInfo;
  Album: PAlbumInfo;
begin
  // Ignore if already cached
  if FCached then Exit;

  // Get all genres
  Rows := FDB.GetTable(SQL_GET_GENRE_CACHE);
  with Rows do while not EOF do
  begin
    New(Genre);
    Genre.Name := FieldAsString(FIELD_NAME);
    Genre.Duration := FieldAsInteger(FIELD_SIZE);
    Genre.Tracks := FieldAsInteger(FIELD_TRACK);
    FGenreCache.Add(Genre);
    Next;
  end;
  Rows.Free;

  // Get all artists
  Rows := FDB.GetTable(SQL_GET_ARTIST_CACHE);
  with Rows do while not EOF do
  begin
    New(Artist);
    Artist.Name := FieldAsString(FIELD_NAME);
    Artist.Duration := FieldAsInteger(FIELD_SIZE);
    Artist.Tracks := FieldAsInteger(FIELD_TRACK);
    Artist.ImageURL := FieldAsString(FIELD_URL);
    FArtistCache.Add(Artist);
    Next;
  end;
  Rows.Free;

  // Get all albums
  Rows := FDB.GetTable(SQL_GET_ALBUM_CACHE);
  with Rows do while not EOF do
  begin
    New(Album);
    Album.Name := FieldAsString(FIELD_NAME);
    Album.Duration := FieldAsInteger(FIELD_SIZE);
    Album.Tracks := FieldAsInteger(FIELD_TRACK);
    Album.Year := FieldAsInteger(FIELD_YEAR);
    Album.ImageURL := FieldAsString(FIELD_URL);
    Album.GenreId := FieldAsInteger(FIELD_GENRE);
    Album.ArtistId := FieldAsInteger(FIELD_ARTIST);
    FAlbumCache.Add(Album);
    Next;
  end;
  Rows.Free;

  // Cache is now ready
  FCached := True;
end;

procedure TAHMMediaSchema.ClearCaches;
var
  i: Integer;
  Genre: PGenreInfo;
  Artist: PArtistInfo;
  Album: PAlbumInfo;
begin
  for i := 0 to Pred(FGenreCache.Count) do
  begin
    Genre := PGenreInfo(FGenreCache[i]);
    Dispose(Genre);
  end;

  for i := 0 to Pred(FArtistCache.Count) do
  begin
    Artist := PArtistInfo(FArtistCache[i]);
    Dispose(Artist);
  end;

  for i := 0 to Pred(FAlbumCache.Count) do
  begin
    Album := PAlbumInfo(FAlbumCache[i]);
    Dispose(Album);
  end;

  FAlbumCache.Clear;
  FArtistCache.Clear;
  FGenreCache.Clear;

  // Cache is now empty
  FCached := False;
end;

procedure TAHMMediaSchema.PrepareInsertQueries;
begin
  if not FPrepared then
  begin
    FInsertMedia := FDB.PrepareSQL(SQL_INSERT_MEDIA);
    FInsertGenre := FDB.PrepareSQL(SQL_INSERT_GENRE);
    FInsertArtist := FDB.PrepareSQL(SQL_INSERT_ARTIST);
    FInsertAlbum := FDB.PrepareSQL(SQL_INSERT_ALBUM);
    FPrepared := True;
  end;
end;

procedure TAHMMediaSchema.ReleaseInsertQueries;
begin
  if FPrepared then
  begin
    FDB.ReleaseSQL(FInsertMedia);
    FDB.ReleaseSQL(FInsertGenre);
    FDB.ReleaseSQL(FInsertArtist);
    FDB.ReleaseSQL(FInsertAlbum);
    FPrepared := False;
  end;
end;

procedure TAHMMediaSchema.InsertMediaItem(Id: Integer; const Name: String;
                                          AlbumId, ArtistId, GenreId: Integer;
                                          const URL: String; Year, Size, Track,
                                          BitRate: Integer; AlbumArt: Boolean);
begin
  FDB.BindSQL(FInsertMedia, 1, Id);
  FDB.BindSQL(FInsertMedia, 2, Name);
  FDB.BindSQL(FInsertMedia, 3, AlbumId);
  FDB.BindSQL(FInsertMedia, 4, ArtistId);
  FDB.BindSQL(FInsertMedia, 5, GenreId);
  FDB.BindSQL(FInsertMedia, 6, URL);
  FDB.BindSQL(FInsertMedia, 7, Year);
  FDB.BindSQL(FInsertMedia, 8, Size);
  FDB.BindSQL(FInsertMedia, 9, Track);
  FDB.BindSQL(FInsertMedia, 10, BitRate);
  FDB.BindSQL(FInsertMedia, 11, AlbumArt);
  FDB.ExecSQL(FInsertMedia);
end;

procedure TAHMMediaSchema.InsertGenre(Id: Integer; const Name: String;
                                      Size, Tracks: Integer);
begin
  FDB.BindSQL(FInsertGenre, 1, Id);
  FDB.BindSQL(FInsertGenre, 2, Name);
  FDB.BindSQL(FInsertGenre, 3, Size);
  FDB.BindSQL(FInsertGenre, 4, Tracks);
  FDB.ExecSQL(FInsertGenre);
end;

procedure TAHMMediaSchema.InsertArtist(Id: Integer; const Name, ImageURL: String;
                                       Size, Tracks: Integer);
begin
  FDB.BindSQL(FInsertArtist, 1, Id);
  FDB.BindSQL(FInsertArtist, 2, Name);
  FDB.BindSQL(FInsertArtist, 3, Size);
  FDB.BindSQL(FInsertArtist, 4, Tracks);
  FDB.BindSQL(FInsertArtist, 5, ImageURL);
  FDB.ExecSQL(FInsertArtist);
end;

procedure TAHMMediaSchema.InsertAlbum(Id: Integer; const Name, ImageURL: String;
                                      ArtistId, GenreId, Year, Size, Tracks: Integer);
begin
  FDB.BindSQL(FInsertAlbum, 1, Id);
  FDB.BindSQL(FInsertAlbum, 2, Name);
  FDB.BindSQL(FInsertAlbum, 3, ArtistId);
  FDB.BindSQL(FInsertAlbum, 4, GenreId);
  FDB.BindSQL(FInsertAlbum, 5, Year);
  FDB.BindSQL(FInsertAlbum, 6, ImageURL);
  FDB.BindSQL(FInsertAlbum, 7, Size);
  FDB.BindSQL(FInsertAlbum, 8, Tracks);
  FDB.ExecSQL(FInsertAlbum);
end;

procedure TAHMMediaSchema.UpdateArtistArt(Id: Integer; const ImageURL: String);
var
  Artist: PArtistInfo;
begin
  // Update artwork art for specified artist
  FDB.ExecSQL(Format(SQL_UPDATE_ARTISTART, [Id, ImageURL]));

  // Update cache details if artists are currently cached
  if FCached then
  begin
    Artist := GetArtistInfo(Id);
    Artist.ImageURL := ImageURL;
  end;
end;

procedure TAHMMediaSchema.UpdateAlbumArt(Id: Integer; const ImageURL: String);
var
  Album: PAlbumInfo;
  Artist: PArtistInfo;
begin
  // Update album art for specified album
  FDB.ExecSQL(Format(SQL_UPDATE_ALBUMART, [Id, ImageURL]));

  // Update cache details if albums are currently cached
  if FCached then
  begin
    Album := GetAlbumInfo(Id);
    Album.ImageURL := ImageURL;

    // Update corresponding artist art if this is missing
    Artist := GetArtistInfo(Album.ArtistId);
    if Artist.ImageURL = '' then UpdateArtistArt(Album.ArtistId, ImageURL);
  end;
end;

function TAHMMediaSchema.ListGenres: TAHMDBResult;
begin
  // Get all genres
  Result := FDB.GetTable(SQL_GET_ALL_GENRES);
end;

function TAHMMediaSchema.ListArtists(GenreId: Integer; CompilationArtists: Boolean): TAHMDBResult;
var
  SQL: String;
begin
  // Get all artists or artists for specified genre
  if GenreId = 0 then SQL := SQL_GET_ALL_ARTISTS
                 else SQL := Format(SQL_GET_ARTISTS_BY_GENRE, [GenreId]);

  // Append clause to show album artists only if required
  if not CompilationArtists then SQL := SQL + SQL_ARTIST_ALBUM;

  // Append sort term
  Result := FDB.GetTable(SQL + SQL_ARTIST_ORDER);
end;

function TAHMMediaSchema.ListAlbums(GenreId, ArtistId: Integer): TAHMDBResult;
begin
  // Get all albums or albums for specified genre or artist or both
  if ArtistId = 0 then
    if GenreId = 0 then
      Result := FDB.GetTable(SQL_GET_ALL_ALBUMS)
    else
      Result := FDB.GetTable(Format(SQL_GET_ALBUMS_BY_GENRE, [GenreId]))
  else
    if GenreId = 0 then
      Result := FDB.GetTable(Format(SQL_GET_ALBUMS_BY_ARTIST, [ArtistId]))
    else
      Result := FDB.GetTable(Format(SQL_GET_ALBUMS_BY_ARTIST_GENRE, [ArtistId, GenreId]));
end;

function TAHMMediaSchema.ListTracks(GenreId, ArtistId, AlbumId: Integer): TAHMDBResult;
var
  Where: String;
begin
  // Get tracks - we can filter by any combination of genre, artist and album
  // which gives us 8 combinations so we concatenate terms to build where clause
  if GenreId <> 0 then Where := Format(SQL_TRACK_GENRE, [GenreId])
                  else Where := '';
  if ArtistId <> 0 then
  begin
    if Where <> '' then Where := Where + ' AND ';
    Where := Where + Format(SQL_TRACK_ARTIST, [ArtistId]);
  end;
  if AlbumId <> 0 then
  begin
    if Where <> '' then Where := Where + ' AND ';
    Where := Where + Format(SQL_TRACK_ALBUM, [AlbumId]);
  end;

  // If where clause is required then add to SQL
  if Where <> '' then Where := ' WHERE ' + Where;

  Result := FDB.GetTable(SQL_GET_ALL_TRACKS + Where);
end;

function TAHMMediaSchema.GetAlbumHash(AlbumId: Integer): String;
var
  Album: PAlbumInfo;
  Artist: PArtistInfo;
begin
  // Set default result - not available
  Result := '';

  // Create a hash of unique album details so we can find this album later
  // when we rebuild the media schema. Hashes album name, track count, year
  // and artist name. Schema must be cached so this can be done in memory
  if FCached then
  begin
    Album := GetAlbumInfo(AlbumId);
    Artist := GetArtistInfo(Album.ArtistId);

    Result := Artist.Name + ':' + Album.Name + ':' +
              IntToStr(Album.Tracks) + ':' + IntToStr(Album.Year);
  end;
end;

end.
